# 初始化数据库
import sqlite3


def get_db_connect():
    return sqlite3.connect(database="../db/OrderDB.db")


# 释放数据库游标，连接
def free_db_connection(cursor_, conn):
    try:
        cursor_.close()
    except Exception as e:
        print(e)
    try:
        conn.close()
    except Exception as e:
        pass


def table_goods_groups(cursor):
    sql = '''
    CREATE TABLE goods_groups
    (
    group_id    INTEGER PRIMARY KEY AUTOINCREMENT,
    group_name  varchar unique,
    group_image varchar
    )
    '''
    cursor.execute(sql)


def table_goods(cursor):
    sql = '''
    CREATE TABLE goods
    (
    goods_id    INTEGER PRIMARY KEY AUTOINCREMENT,
    group_name  varchar,
    goods_name  varchar,
    price       INTEGER,
    goods_image varchar
    )
    '''
    cursor.execute(sql)


def table_orders_group(cursor):
    sql = '''
    CREATE TABLE orders_group
    (
    order_group_id INTEGER PRIMARY KEY AUTOINCREMENT,
    create_date    TimeStamp NOT NULL DEFAULT (datetime('now', 'localtime')),
    order_path     VARCHAR,
    remark         VARCHAR
    )
    '''
    cursor.execute(sql)


def table_orders(cursor):
    sql = '''
    CREATE TABLE orders
    (
    order_group_id INTEGER,
    good_id        INTEGER,
    good_name      VARCHAR,
    price          INTEGER,
    buy_number     INTEGER,
    sum_price      VARCHAR
    )
    '''
    cursor.execute(sql)


def table_config(cursor):
    sql = '''
    CREATE TABLE config
    (
    arg   varchar,
    value varchar
    )
    '''
    cursor.execute(sql)


def insert_default_values(cursor):
    welcome_image = '''
    INSERT INTO config (arg, value) VALUES ('welcome_image', '../assets/default_welcome_image.jpg')
    '''
    order_path = '''
    INSERT INTO config (arg, value) VALUES ('orders_save_path', '../bundle/')
    '''
    app_path = '''
    INSERT INTO config (arg, value) VALUES ('app_path', '')
    '''
    cursor.execute(welcome_image)
    cursor.execute(order_path)
    cursor.execute(app_path)


if __name__ == '__main__':
    try:
        con = get_db_connect()
        cursor = con.cursor()
        # 开始创建表
        table_goods_groups(cursor)
        table_goods(cursor)
        table_orders_group(cursor)
        table_orders(cursor)
        table_config(cursor)
        print("以创建完所有表。")
        con.commit()
        # 插入重要的参数
        insert_default_values(cursor)
        print("以插入重要的参数。")
        con.commit()

    except Exception as e:
        free_db_connection(cursor, con)
        print("初始化失败")
